# 随机一百个中文字符姓名，存入到列表中
import random
import string
import warnings

warnings.filterwarnings('ignore')

name_list = []
for i in range(50):
    # 随机生成四个字母
    name = ''.join(random.sample(string.ascii_letters, 4))
    name_list.append(name)

# 使用pandas读取文件夹中的xlsx文件。
import pandas as pd
import os

# 读取Data文件夹的所有xlsx文件
df = pd.concat(pd.read_excel(f"Data/{file}") for file in os.listdir('Data'))
# 取出姓名、课程名称、学号、平时成绩、期末成绩、总评成绩、学分、绩点、课程类别、任课教师为dataframe
name_df = df[['姓名', '课程名称', '学号', '平时成绩', '期末成绩', '总评成绩', '绩点', '课程类别', '任课教师']]

# 筛选name_df中每一个姓名对应的记录
names = name_df['姓名'].unique().tolist()
for name in names:
    # 筛选出name_df中姓名为name的记录,将对应记录的姓名替换为name_list中的随机一个
    name_df = name_df.replace(to_replace=name, value=random.choice(name_list))

# 将平时成绩为空值的改为100
name_df['平时成绩'] = name_df['平时成绩'].fillna(100)
# 将期末成绩为优秀的改为95，良好改为85，及格改为60
trans_dict = {
    "优秀": 95,
    "良好": 85,
    "中等": 75,
    "及格": 60,
    "不及格": 0,
}
name_df['期末成绩'] = name_df['期末成绩'].apply(lambda x: trans_dict.get(x, x))
name_df['总评成绩'] = name_df['期末成绩'].apply(lambda x: trans_dict.get(x, x))
name_df['平时成绩'] = name_df['期末成绩'].apply(lambda x: trans_dict.get(x, x))

name_df['学号'] = name_df['学号'].astype(str)  # 学号转为字符串类型
# 将成绩列数据类型改为float
name_df['平时成绩'] = name_df['平时成绩'].astype(float)
name_df['期末成绩'] = name_df['期末成绩'].astype(float)
name_df['总评成绩'] = name_df['总评成绩'].astype(float)
name_df['绩点'] = name_df['绩点'].astype(float)
name_df = name_df.dropna()  # 删除空值
print(name_df)

# 将name_df中的学号、姓名、课程名称平时成绩、期末成绩、总评成绩、绩点存入到mysql数据库中。
import pymysql

# 连接数据库
db = pymysql.connect(host="localhost", user="root", password="123456", db="student_portrait_system", charset="utf8")

# 创建游标
cursor = db.cursor()

# # 循环插入数据
# for i in range(len(name_df)):
#     sql = "INSERT INTO stu_info (stu_id, stu_name, course_name, com_grade, end_grade, total_grade, grade_point) VALUES (%s, %s, %s, %s, %s, %s, %s)"
#     cursor.execute(sql, (
#         name_df.iloc[i]['学号'], name_df.iloc[i]['姓名'], name_df.iloc[i]['课程名称'], name_df.iloc[i]['平时成绩'],
#         name_df.iloc[i]['期末成绩'], name_df.iloc[i]['总评成绩'], name_df.iloc[i]['绩点']))
#     db.commit()
#     print("插入成功")

# 将name_df中的课程名称、任课教师、课程类别去重
courses = name_df[['课程名称', '任课教师', '课程类别']]
courses = courses.drop_duplicates(subset=['课程名称'])

# 循环插入数据到mysql数据库中
for i in range(len(courses)):
    sql = "INSERT INTO course_info (course_name, cat, teacher) VALUES (%s, %s, %s)"
    cursor.execute(sql, (courses.iloc[i]['课程名称'], courses.iloc[i]['课程类别'], courses.iloc[i]['任课教师']))
    db.commit()
    print("插入成功")
